﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WXY.Course.Model;

namespace WXY.Course.DAL
{
    /// <summary>
    /// 模块
    /// </summary>
    public class ModuleDAL
    {
        private static SqlHelper sqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CourseDB"].ConnectionString);

        private const string SQL_INSERT = @"INSERT INTO TModule(Name,CreateTime,UpdateTime)
                                            values(@Name,@CreateTime,@UpdateTime)";

        private const string SQL_UPDATE = @"UPDATE TModule SET
                                            Name=@Name,UpdateTime=getdate()
                                            WHERE Code=@Code";

        private const string SQL_DELETE = @"DELETE FROM TModule
                                            WHERE CODE=@CODE";

        private const string SQL_GET = "select * from TModule where Code=@Code";

        private const string SQL_GETLIST = "select * from TModule";

        private const string SQL_GETPAGELIST = @"select top (@PageSize) * from (
                            select row_number() over(order by code desc) as rownumber,* from TModule 
                            where Name like @Name
                            and (CreateTime between @BeginTime and @EndTime)
                         )
                         t where rownumber > (@PageIndex -1 ) * @PageSize";

        private const string SQL_GETPAGECOUNT = @"select count(*) from TModule 
                            where Name like @Name
                            and (CreateTime between @BeginTime and @EndTime)";

        public static int Insert(ModuleInfo module)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Name", module.Name),
                new SqlParameter("@CreateTime", DateTime.Now),
                new SqlParameter("@UpdateTime", DateTime.Now)
            };

            return sqlHelper.ExecuteNonQuery(SQL_INSERT, CommandType.Text, parameters);
        }

        public static int Update(ModuleInfo module)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Code", module.Code),
                new SqlParameter("@Name", module.Name),
                new SqlParameter("@UpdateTime", DateTime.Now)
            };

            return sqlHelper.ExecuteNonQuery(SQL_UPDATE, CommandType.Text, parameters);
        }

        public static int Delete(int code)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Code", code)
            };

            return sqlHelper.ExecuteNonQuery(SQL_DELETE, CommandType.Text, parameters);
        }

        /// <summary>
        /// 获取指定Code的模块信息
        /// </summary>
        /// <returns></returns>
        public static ModuleInfo Get(int code)
        {
            SqlParameter parameter = new SqlParameter("@Code", code);

            return sqlHelper.ExecuteQuery(SQL_GET, CommandType.Text, (reader) =>
            {
                ModuleInfo module = null;
                while (reader.Read())
                {
                    module = new ModuleInfo();
                    module.Code = Convert.ToInt32(reader["Code"]);
                    module.Name = reader["Name"].ToString();
                    module.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    module.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                }
                return module;
            }, parameter);
        }

        /// <summary>
        /// 获取模块列表
        /// </summary>
        public static IList<ModuleInfo> GetList()
        {
            return sqlHelper.ExecuteQuery(SQL_GETLIST, CommandType.Text, (reader) =>
            {
                IList<ModuleInfo> list = new List<ModuleInfo>();
                while (reader.Read())
                {
                    ModuleInfo module = new ModuleInfo();
                    module.Code = Convert.ToInt32(reader["Code"]);
                    module.Name = reader["Name"].ToString();
                    module.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    module.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    list.Add(module);
                }
                return list;
            }, null);
        }

        public static IList<ModuleInfo> GetPageList(string name, PageParams pageParams)
        {
            SqlParameter[] parameter = {
                new SqlParameter("@Name", string.Format("%{0}%",name)),
                new SqlParameter("@BeginTime", pageParams.BeginTime.HasValue ?pageParams.BeginTime.Value: DateTime.Now.AddYears(-100)  ),
                new SqlParameter("@EndTime",  pageParams.EndTime.HasValue ?  pageParams.EndTime.Value :DateTime.Now.AddYears(100)),
                new SqlParameter("@PageIndex",  pageParams.PageIndex),
                new SqlParameter("@PageSize",  pageParams.PageSize),
            };
            pageParams.RecordCount = GetPageCount(name, pageParams);
            return sqlHelper.ExecuteQuery(SQL_GETPAGELIST, CommandType.Text, (reader) =>
            {
                IList<ModuleInfo> list = new List<ModuleInfo>();
                while (reader.Read())
                {
                    ModuleInfo module = new ModuleInfo();
                    module.Code = Convert.ToInt32(reader["Code"]);
                    module.Name = reader["Name"].ToString();
                    module.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    module.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    list.Add(module);
                }
                return list;
            }, parameter);
        }

        public static int GetPageCount(string name, PageParams pageParams)
        {
            SqlParameter[] parameter = {
                new SqlParameter("@Name", string.Format("%{0}%",name)),
                new SqlParameter("@BeginTime", pageParams.BeginTime.HasValue ?pageParams.BeginTime.Value: DateTime.Now.AddYears(-100)  ),
                new SqlParameter("@EndTime",  pageParams.EndTime.HasValue ?  pageParams.EndTime.Value :DateTime.Now.AddYears(100))
            };
            object o = sqlHelper.ExecuteScalar(SQL_GETPAGECOUNT, CommandType.Text, parameter);

            return Convert.ToInt32(o);
        }

    }
}
